﻿<%@ WebHandler Language="C#" Class="DealFaultBillTransferReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 返件查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.4.25
 **/

public class DealFaultBillTransferReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
 SELECT mfg.mfgordername,db.engineno,db.enginefamily,db.dealfaultbillname,db.productname,
        p.description productdescription,db.productbatchno,dbt.fromqty,
        f.factoryname sendfactoryname,f2.factoryname tofactoryname,
        e6.fullname createemp,db.createdate,
        CASE WHEN db.transferstatus = 0 THEN '待返出单位技术审核'
             WHEN db.transferstatus = 1 THEN '待返出单位检验确认'
             WHEN db.transferstatus = 2 THEN '待返出单位返出'
             WHEN db.transferstatus = 3 THEN '待接收单位接收'
             WHEN db.transferstatus = 4 THEN '待接收单位完成'
             WHEN db.transferstatus = 8 AND dbt.faultcheckreportno IS NULL THEN '待检验故检报告'
             WHEN db.transferstatus = 8 AND dbt.faultcheckreportno IS NOT NULL AND dbt.dealfileno IS NULL THEN '待工程部修理文件'
             WHEN db.transferstatus = 5 AND dbt.dealfileno IS NOT NULL THEN '待接收单位修理'
             WHEN db.transferstatus = 5 AND dbt.faultcheckdate IS NULL THEN '待接收单位返回'
             WHEN db.transferstatus = 6 THEN '待返出单位领取'
             WHEN db.transferstatus = 7 THEN '现场已领取'
        END status,
        e.fullname fromemp,dbt.fromtime,dbt.requirefinishtime,e2.fullname recvfactoryempname,dbt.recvfactorytime,dbt.estimatedcompletedate,
        e3.fullname finishempname,dbt.finishtime,e4.fullname recvfactoryreturnempname,dbt.recvfactoryreturntime,
        e5.fullname acceptteamname,dbt.accepttime,dbt.revrejectreason,dbt.revreturnrejectreason,
        DECODE(dbt.faultcheckempid,NULL,'否','是') ischeck,dbt.entitystoragelocation,dbt.faultcheckreportno,dbt.dealfileno,
        dbt.faultcheckdate,dbt.faultcheckreportnodate,
        CASE WHEN dbt.faultcheckdate IS NOT NULL THEN dbt.finishtime ELSE NULL END jsdate,
        CASE WHEN dbt.faultcheckdate IS NOT NULL THEN e3.fullname ELSE '' END jsemp,
        e7.fullname auditemp,dbt.audittime,e8.fullname confirmemp,dbt.confirmtime,e9.fullname faultcheckemp,e10.fullname tjgjemp,
        CASE WHEN NVL(db.isuse,0) = 0 THEN '在用'
             WHEN NVL(db.isuse,0) = 1 THEN '无效'
             WHEN NVL(db.isuse,0) = 2 THEN '作废'
        END dbstatus
FROM DealFaultBill db
LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = db.mfgorderid
LEFT JOIN Sessionvalues sv ON sv.employeeid = db.createemployeeid
LEFT JOIN mes_factory_mapping f ON f.factoryid = sv.factoryid --申请分厂
LEFT JOIN mes_factory_mapping f2 ON f2.factoryid = db.returnfactoryid --返件分厂
LEFT JOIN employee e ON e.employeeid = dbt.FromEmpID --返出人
LEFT JOIN employee e2 ON e2.employeeid = dbt.RecvFactoryEmpID --返件接收人
LEFT JOIN employee e3 ON e3.employeeid = dbt.finishempid --完成人
LEFT JOIN employee e4 ON e4.employeeid = dbt.RecvFactoryReturnEmpID --返回人
LEFT JOIN employee e5 ON e5.employeeid = dbt.acceptteamid--领取人
LEFT JOIN employee e6 ON e6.employeeid = db.createemployeeid --编制人
LEFT JOIN employee e7 ON e7.employeeid = dbt.auditempid --审核人
LEFT JOIN employee e8 ON e8.employeeid = dbt.confirmempid --确认人
LEFT JOIN employee e9 ON e9.employeeid = dbt.faultcheckreportnoempid --故检报告确认人
LEFT JOIN employee e10 ON e10.employeeid = dbt.faultcheckempid --提交故检人
LEFT JOIN productbase pb ON pb.productname = db.productname
LEFT JOIN product p ON p.productid = pb.revofrcdid
WHERE 1=1";
        //申请单位
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //返件单位
        if (jObject["tofactoryid"] != null && jObject["tofactoryid"].ToString() != "")
        {
            sql += string.Format(" AND db.returnfactoryid = '{0}'", jObject["tofactoryid"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND db.enginefamily LIKE '%{0}%' ", jObject["productfamilyname"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND mfg.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND db.engineno = '{0}'", jObject["engineno"].ToString());
        }

        //排故单号
        if (jObject["dealfaultbillname"] != null && jObject["dealfaultbillname"].ToString() != "")
        {
            sql += string.Format(" AND db.dealfaultbillname = '{0}'", jObject["dealfaultbillname"].ToString());
        }

        //排故进展
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            switch (status)
            {
                case "待返出单位技术审核":
                    sql += " AND db.transferstatus = 0";
                    break;
                case "待返出单位检验确认":
                    sql += " AND db.transferstatus = 1";
                    break;
                case "待返出单位返出":
                    sql += " AND db.transferstatus = 2";
                    break;
                case "待接收单位接收":
                    sql += " AND db.transferstatus = 3";
                    break;
                case "待接收单位完成":
                    sql += " AND db.transferstatus = 4";
                    break;
                case "待检验故检报告":
                    sql += " AND db.transferstatus = 8 AND dbt.faultcheckreportno IS NULL";
                    break;
                case "待工程部修理文件":
                    sql += " AND db.transferstatus = 8 AND dbt.faultcheckreportno IS NOT NULL AND dbt.dealfileno IS NULL";
                    break;
                case "待接收单位修理":
                    sql += " AND db.transferstatus = 5 AND dbt.dealfileno IS NOT NULL";
                    break;
                case "待接收单位返回":
                    sql += " AND db.transferstatus = 5 AND dbt.faultcheckdate IS NULL";
                    break;
                case "待返出单位领取":
                    sql += " AND db.transferstatus = 6";
                    break;
                case "现场已领取":
                    sql += " AND db.transferstatus = 7";
                    break;
                default:
                    break;
            }
        }

        //是否故检
        if (jObject["isgj"] != null && jObject["isgj"].ToString() != "")
        {
            string isgj = jObject["isgj"].ToString();
            switch (isgj)
            {
                case "是":
                    sql += " AND dbt.faultcheckempid IS NOT NULL";
                    break;
                case "否":
                    sql += " AND dbt.faultcheckempid IS NULL";
                    break;
                default:
                    break;
            }
        }

        //编制人
        if (jObject["bianzhiren"] != null && jObject["bianzhiren"].ToString() != "")
        {
            sql += string.Format(" AND e6.fullname ='{0}'", jObject["bianzhiren"].ToString());
        }

        //编制时间
        if (jObject["bianzhitime"] != null && jObject["bianzhitime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["bianzhitime"].ToString());
            sql += string.Format(" AND TRUNC(db.createdate) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(db.createdate) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //审核人
        if (jObject["auditemp"] != null && jObject["auditemp"].ToString() != "")
        {
            sql += string.Format(" AND e7.fullname = '{0}'", jObject["auditemp"].ToString());
        }

        //审核时间
        if (jObject["audittime"] != null && jObject["audittime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["audittime"].ToString());
            sql += string.Format(" AND TRUNC(dbt.audittime) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.audittime) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //确认人
        if (jObject["confirmemp"] != null && jObject["confirmemp"].ToString() != "")
        {
            sql += string.Format(" AND e8.fullname = '{0}'", jObject["confirmemp"].ToString());
        }

        //确认时间
        if (jObject["confirmtime"] != null && jObject["confirmtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["confirmtime"].ToString());
            sql += string.Format(" AND TRUNC(dbt.confirmtime) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.confirmtime) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //返出人
        if (jObject["fanchuren"] != null && jObject["fanchuren"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname = '{0}'", jObject["fanchuren"].ToString());
        }

        //返出时间
        if (jObject["fanchutime"] != null && jObject["fanchutime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["fanchutime"].ToString());
            sql += string.Format(" AND TRUNC(dbt.FromTime) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.FromTime) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //接收人
        if (jObject["jieshouren"] != null && jObject["jieshouren"].ToString() != "")
        {
            sql += string.Format(" AND e2.fullname = '{0}'", jObject["jieshouren"].ToString());
        }

        //接收时间
        if (jObject["jieshoutime"] != null && jObject["jieshoutime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["jieshoutime"].ToString());
            sql += string.Format(" AND TRUNC(dbt.recvfactorytime) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.recvfactorytime) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //完成人
        if (jObject["wanchengren"] != null && jObject["wanchengren"].ToString() != "")
        {
            sql += string.Format(" AND e3.fullname = '{0}'", jObject["wanchengren"].ToString());
        }

        //完成时间
        if (jObject["wanchengtime"] != null && jObject["wanchengtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["wanchengtime"].ToString());
            sql += string.Format(" AND TRUNC(dbt.finishtime) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.finishtime) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //返回人
        if (jObject["fanhuiren"] != null && jObject["fanhuiren"].ToString() != "")
        {
            sql += string.Format(" AND e4.fullname = '{0}'", jObject["fanhuiren"].ToString());
        }

        //返回时间
        if (jObject["fanhuitime"] != null && jObject["fanhuitime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["fanhuitime"].ToString());
            sql += string.Format(" AND TRUNC(dbt.recvFactoryReturnTime) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.recvFactoryReturnTime) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //领取人
        if (jObject["lingquren"] != null && jObject["lingquren"].ToString() != "")
        {
            sql += string.Format(" AND e5.fullname = '{0}'", jObject["lingquren"].ToString());
        }

        //领取时间
        if (jObject["lingqutime"] != null && jObject["lingqutime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["lingqutime"].ToString());
            sql += string.Format(" AND TRUNC(dbt.accepttime) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.accepttime) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //提交故检人
        if (jObject["faultcheckemp"] != null && jObject["faultcheckemp"].ToString() != "")
        {
            sql += string.Format(" AND e10.fullname = '{0}'", jObject["faultcheckemp"].ToString());
        }

        //提交故检时间
        if (jObject["faultcheckdate"] != null && jObject["faultcheckdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["faultcheckdate"].ToString());
            sql += string.Format(" AND TRUNC(dbt.faultcheckdate) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.faultcheckdate) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //故检报工录入人
        if (jObject["faultcheckreportnoemp"] != null && jObject["faultcheckreportnoemp"].ToString() != "")
        {
            sql += string.Format(" AND e9.fullname = '{0}'", jObject["faultcheckreportnoemp"].ToString());
        }

        //故检报工录入时间
        if (jObject["faultcheckreportnodate"] != null && jObject["faultcheckreportnodate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["faultcheckreportnodate"].ToString());
            sql += string.Format(" AND TRUNC(dbt.faultcheckreportnodate) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.faultcheckreportnodate) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //修理文件录入人
        if (jObject["jsemp"] != null && jObject["jsemp"].ToString() != "")
        {
            sql += string.Format(" AND dbt.faultcheckempid IS NOT NULL AND e3.fullname = '{0}'", jObject["jsemp"].ToString());
        }

        //修理文件录入时间
        if (jObject["faultcheckreportnodate"] != null && jObject["faultcheckreportnodate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["faultcheckreportnodate"].ToString());
            sql += " AND dbt.faultcheckempid IS NOT NULL";
            sql += string.Format(" AND TRUNC(dbt.finishtime) >= to_date('{0}','yyyy/MM/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(dbt.finishtime) <= to_date('{0}','yyyy/MM/dd')", arr[1].ToString());
        }

        //排故单状态
        if (jObject["dbstatus"] != null && jObject["dbstatus"].ToString() != "")
        {
            sql += string.Format(" AND NVL(db.isuse,0) = '{0}'", jObject["dbstatus"].ToString());
        }

        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dt.Columns["mfgordername"].Caption = "订单号";
        dt.Columns["engineno"].Caption = "发动机号";
        dt.Columns["enginefamily"].Caption = "机型";
        dt.Columns["dealfaultbillname"].Caption = "排故单号";
        dt.Columns["productname"].Caption = "组合件号";
        dt.Columns["productbatchno"].Caption = "批次号";
        dt.Columns["fromqty"].Caption = "发出数量";
        dt.Columns["sendfactoryname"].Caption = "申请单位";
        dt.Columns["tofactoryname"].Caption = "返件单位";
        dt.Columns["createemp"].Caption = "编制人";
        dt.Columns["createdate"].Caption = "编制时间";
        dt.Columns["status"].Caption = "返件状态";
        dt.Columns["FromEmp"].Caption = "返出人";
        dt.Columns["FromTime"].Caption = "返出时间";
        dt.Columns["RequireFinishTime"].Caption = "要求完成时间";
        dt.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dt.Columns["RecvFactoryEmpname"].Caption = "接收人";
        dt.Columns["RecvFactoryTime"].Caption = "接收时间";
        dt.Columns["finishempname"].Caption = "完成人";
        dt.Columns["finishtime"].Caption = "完成时间";
        dt.Columns["RecvFactoryReturnEmpname"].Caption = "返回人";
        dt.Columns["recvFactoryReturnTime"].Caption = "返回时间";
        dt.Columns["acceptteamname"].Caption = "领取人";
        dt.Columns["AcceptTime"].Caption = "领取时间";
        dt.Columns["revrejectreason"].Caption = "接收退回原因";
        dt.Columns["revreturnrejectreason"].Caption = "完成退回原因";
        dt.Columns["productdescription"].Caption = "零件名称";
        dt.Columns["ischeck"].Caption = "是否故检";
        dt.Columns["entitystoragelocation"].Caption = "实物存放地";
        dt.Columns["faultcheckreportno"].Caption = "故检报告号";
        dt.Columns["dealfileno"].Caption = "修理文件号";
        dt.Columns["faultcheckdate"].Caption = "提交故检时间";
        dt.Columns["faultcheckreportnodate"].Caption = "故检报告号录入日期";
        dt.Columns["jsdate"].Caption = "修理文件录入时间";
        dt.Columns["jsemp"].Caption = "修理文件录入人";
        dt.Columns["auditemp"].Caption = "审核人";
        dt.Columns["audittime"].Caption = "审核时间";
        dt.Columns["confirmemp"].Caption = "确认人";
        dt.Columns["confirmtime"].Caption = "确认时间";
        dt.Columns["faultcheckemp"].Caption = "故检报告录入人";
        dt.Columns["tjgjemp"].Caption = "提交故检人";
        dt.Columns["dbstatus"].Caption = "排故单状态";
        ExportHelper.ExportExcel(dt, jObject["factoryname"].ToString() + "返件查询报表");
    }
}